﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Reporting.WebForms;
using log4net;

public partial class monthReport : System.Web.UI.Page
{
    #region Private methods
    private ReportViewer rpViewer = null;
    private ReportParameter parm_Logo = null;
    private static readonly ILog logger = LogManager.GetLogger(typeof(monthReport).Name);
    /// <summary>
    /// Load Ham ddl
    /// </summary>
    private void LoadFloor()
    {
        trNhanVien.Visible = false;
        trHam.Visible = true;

        IList<ParkingFloor> list = new ParkingFloor().GetAll();
        ddlFloor.DataSource = list;
        ddlFloor.DataTextField = "Name";
        ddlFloor.DataValueField = "ID";
        ddlFloor.DataBind();

    }

    private void LoadEmployee()
    {
        trHam.Visible = false;
        trNhanVien.Visible = true;

        List<Employee> list = (new EmployeeBLL()).GetAll();
        var empList = from item in list
                      where item.PermissionID.Equals("01")
                      select new
                      {
                          ID = item.Id,
                          Name = String.Format("{0} {1} ({2})", item.FirstName, item.LastName, item.Code)
                      };
        ddlEmp.DataSource = empList;
        ddlEmp.DataTextField = "Name";
        ddlEmp.DataValueField = "ID";
        ddlEmp.DataBind();

    }

    private DateTime GetReportDateTime(DateTime dt, TextBox txtHour, int seconds, int milliseconds)
    {
        if (String.IsNullOrEmpty(txtHour.Text))
        {
            return dt;
        }
        string[] arr = txtHour.Text.Split(':');
        if (arr.Length != 2) return dt;
        return new DateTime(dt.Year, dt.Month, dt.Day, int.Parse(arr[0]),
                                int.Parse(arr[1]), seconds, milliseconds);
    }

    /// <summary>
    /// Is called when click view button
    /// </summary>
    private void Export()
    {
        rpViewer = new ReportViewer();
        rpViewer.Width = 940;
        rpViewer.Height = 440;
        rpViewer.ShowRefreshButton = false;
        rpViewer.Visible = false;
        pnlReportViewer.Controls.Clear();
        pnlReportViewer.Controls.Add(rpViewer);

        DateTime dtFrom = DateTime.Now;
        DateTime dtTo = DateTime.Now;

        if (!Utils.IsDate(txtFrom.Text, out dtFrom) ||
            !Utils.IsDate(txtTo.Text, out dtTo))
        {
            lblMsg.Text = "Sai định dạng ngày/tháng/năm";
            return;
        }

        dtFrom = GetReportDateTime(dtFrom, txtFromHour, 0,0);
        dtTo = GetReportDateTime(dtTo, txtToHour, 59, 999);

        DateTime reportDate = Utils.DBDate;
        lblMsg.Text = String.Empty;
        if (dtFrom > dtTo)
        {
            lblMsg.Text = "Điều kiện báo cáo không phù hợp.";
            rpViewer.Visible = false;
            return;
        }
        rpViewer.Visible = true;
        string report = ddlReport.SelectedValue;
        string logoPath = @"file:///" + HttpContext.Current.Server.MapPath(Constants.REPORT_PATH + "Logo.png");
        parm_Logo = new ReportParameter("parm_Logo", logoPath);
        rpViewer.LocalReport.EnableExternalImages = true;
        switch (report)
        {
            case "01":
                //provide local report information to viewer
                rpViewer.LocalReport.ReportPath = HttpContext.Current.Server.MapPath(Constants.REPORT_PATH + "PrepaidGroupByFloor.rdlc");
                //params
                ReportParameter[] param = new ReportParameter[4];
                param[0] = new ReportParameter("parm_ReportDate", reportDate.ToString("HH:mm dd/MM/yyyy"));
                param[1] = new ReportParameter("parm_From", dtFrom.ToString("HH:mm dd/MM/yyyy"));
                param[2] = new ReportParameter("parm_To", dtTo.ToString("HH:mm dd/MM/yyyy"));
                param[3] = parm_Logo;
                rpViewer.LocalReport.SetParameters(param);
                //prepare report data source
                ReportDataSource rds = new ReportDataSource();
                rds.Name = "dsReport_PrepaidGroupFloor";
                rds.Value = GetResourceReport01(dtFrom, dtTo);
                rpViewer.LocalReport.DataSources.Add(rds);
                rpViewer.Visible = true;
                break;
            case "02":
                string floor = ddlFloor.SelectedItem.Text;

                //provide local report information to viewer
                rpViewer.LocalReport.ReportPath = HttpContext.Current.Server.MapPath(Constants.REPORT_PATH + "PrepaidEachFloor.rdlc");
                //params
                ReportParameter[] param2 = new ReportParameter[5];

                param2[0] = new ReportParameter("parm_ReportDate", reportDate.ToString("HH:mm dd/MM/yyyy"));
                param2[1] = new ReportParameter("parm_From", dtFrom.ToString("HH:mm dd/MM/yyyy"));
                param2[2] = new ReportParameter("parm_To", dtTo.ToString("HH:mm dd/MM/yyyy"));
                param2[3] = new ReportParameter("parm_Floor", floor);
                param2[4] = parm_Logo;
                rpViewer.LocalReport.SetParameters(param2);
                //prepare report data source
                ReportDataSource rds2 = new ReportDataSource();
                rds2.Name = "dsReport_PrepaidGroupFloor";
                rds2.Value = GetResourceReport02(dtFrom, dtTo, floor);
                rpViewer.LocalReport.DataSources.Add(rds2);
                rpViewer.Visible = true;
                break;
            case "VangLaiGroup":
                ThongKeSoLuongXeVangLai(dtFrom, dtTo, reportDate);
                break;
            case "VangLai":
                ThongKeSoLuongXeVangLaiTheoHam(dtFrom, dtTo, reportDate, ddlFloor.SelectedItem.Text);
                break;
            case "TKSoLuongXeGroupByFloor":
                TKSoLuongXeGroupByFloor(dtFrom, dtTo, reportDate);
                break;
            case "TKTongDoanhThuXe":
                TKTongDoanhThuXe(dtFrom, dtTo, reportDate);
                break;
            case "XeChuaThanhToan":
                XeChuaThanhToan(reportDate);
                break;
            case "QLBaoVeTrongThang":
                QLBaoVeTrongThang(dtFrom, dtTo, reportDate);
                break;
            case "QLBaoVeTrongNam":
                QLBaoVeTrongNam(dtFrom, dtTo, reportDate);
                break;
            default:
                break;
        }

    }

    #endregion

    #region QLBaoVeTrongNam
    private DataTable GetResourceQLBaoVeTrongNam(DateTime dtFrom, DateTime dtTo)
    {
        string sql = @"SELECT Thang, Nam, SUM(TotalTime) AS ThoiGian FROM
                        (
                         SELECT MONTH([LogoutTime]) AS Thang,
                           YEAR([LogoutTime]) AS Nam,
                            (DATEDIFF (HOUR , [LoginTime] , [LogoutTime] ) 
                           +DATEDIFF (MINUTE , [LoginTime] , [LogoutTime])/60.0) AS TotalTime  
                         FROM [Working]
                         WHERE EmployeeId=@EmployeeId
                        AND LoginTime>=@FromDate 
                        AND LogoutTime<=@ToDate
                        )tmp
                        GROUP BY Thang, Nam
                        ORDER BY Nam, Thang";
        SqlParameter[] parms = new SqlParameter[3];
        parms[0] = new SqlParameter("FromDate", SqlDbType.DateTime);
        parms[0].Value = dtFrom;

        parms[1] = new SqlParameter("ToDate", SqlDbType.DateTime);
        parms[1].Value = dtTo;

        parms[2] = new SqlParameter("EmployeeId", SqlDbType.UniqueIdentifier);
        if (!String.IsNullOrEmpty(ddlEmp.SelectedValue))
        {
            parms[2].Value = new Guid(ddlEmp.SelectedValue);
        }
        else
        {
            parms[2].Value = Guid.Empty;
        }


        SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, parms);
        DataTable dt = new DataTable();
        dt.Load(reader);

        return dt;
    }

    private void QLBaoVeTrongNam(DateTime dtFrom, DateTime dtTo, DateTime reportDate)
    {
        //provide local report information to viewer
        rpViewer.LocalReport.ReportPath = HttpContext.Current.Server.MapPath(Constants.REPORT_PATH + "QLBaoVeTrongNam.rdlc");
        Guid empId = Guid.Empty;
        if (!String.IsNullOrEmpty(ddlEmp.SelectedValue))
        {
            empId = new Guid(ddlEmp.SelectedValue);
        }
        Employee emp = new EmployeeBLL().GetById(empId);
        string empCode = String.Empty;
        string empName = String.Empty;
        if (emp != null)
        {
            empCode = emp.Code;
            empName = String.Format("{0} {1}", emp.FirstName, emp.LastName);
        }
        //params
        ReportParameter[] param = new ReportParameter[6];
        param[0] = new ReportParameter("parm_ReportDate", reportDate.ToString("HH:mm dd/MM/yyyy"));
        param[1] = new ReportParameter("parm_From", dtFrom.ToString("HH:mm dd/MM/yyyy"));
        param[2] = new ReportParameter("parm_To", dtTo.ToString("HH:mm dd/MM/yyyy"));
        param[3] = new ReportParameter("parm_EmpName", empName);
        param[4] = new ReportParameter("parm_EmpCode", empCode);
        param[5] = parm_Logo;
        rpViewer.LocalReport.SetParameters(param);
        //prepare report data source
        ReportDataSource rds = new ReportDataSource();
        rds.Name = "dsReport_QLBaoVeTrongNam";
        rds.Value = GetResourceQLBaoVeTrongNam(dtFrom, dtTo);
        rpViewer.LocalReport.DataSources.Add(rds);
        rpViewer.Visible = true;
    }
    #endregion

    #region QLBaoVeTrongThang
    private DataTable GetResourceQLBaoVeTrongThang(DateTime dtFrom, DateTime dtTo)
    {
        string sql = @"SELECT [LoginTime] AS GioVao
                          ,[LogoutTime] AS GioRa
                          ,(DATEDIFF (HOUR , [LoginTime] , [LogoutTime] ) 
                      + DATEDIFF (MINUTE , [LoginTime] , [LogoutTime])/60.0) AS ThoiGian
                    FROM [Working]
                    WHERE EmployeeId=@EmployeeId
                    AND LoginTime>=@FromDate 
                    AND LogoutTime<=@ToDate
                    ORDER BY LoginTime, LogoutTime";
        SqlParameter[] parms = new SqlParameter[3];
        parms[0] = new SqlParameter("FromDate", SqlDbType.DateTime);
        parms[0].Value = dtFrom;

        parms[1] = new SqlParameter("ToDate", SqlDbType.DateTime);
        parms[1].Value = dtTo;

        parms[2] = new SqlParameter("EmployeeId", SqlDbType.UniqueIdentifier);
        if (!String.IsNullOrEmpty(ddlEmp.SelectedValue))
        {
            parms[2].Value = new Guid(ddlEmp.SelectedValue);
        }
        else
        {
            parms[2].Value = Guid.Empty;
        }

        SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, parms);
        DataTable dt = new DataTable();
        dt.Load(reader);

        return dt;
    }

    private void QLBaoVeTrongThang(DateTime dtFrom, DateTime dtTo, DateTime reportDate)
    {
        //provide local report information to viewer
        rpViewer.LocalReport.ReportPath = HttpContext.Current.Server.MapPath(Constants.REPORT_PATH + "QLBaoVeTrongThang.rdlc");
        Guid empId = Guid.Empty;
        if (!String.IsNullOrEmpty(ddlEmp.SelectedValue))
        {
            empId = new Guid(ddlEmp.SelectedValue);
        }
        Employee emp = new EmployeeBLL().GetById(empId);
        string empCode = String.Empty;
        string empName = String.Empty;
        if (emp != null)
        {
            empCode = emp.Code;
            empName = String.Format("{0} {1}", emp.FirstName, emp.LastName);
        }
        //params
        ReportParameter[] param = new ReportParameter[6];
        param[0] = new ReportParameter("parm_ReportDate", reportDate.ToString("HH:mm dd/MM/yyyy"));
        param[1] = new ReportParameter("parm_From", dtFrom.ToString("HH:mm dd/MM/yyyy"));
        param[2] = new ReportParameter("parm_To", dtTo.ToString("HH:mm dd/MM/yyyy"));
        param[3] = new ReportParameter("parm_EmpName", empName);
        param[4] = new ReportParameter("parm_EmpCode", empCode);
        param[5] = parm_Logo;
        rpViewer.LocalReport.SetParameters(param);
        //prepare report data source
        ReportDataSource rds = new ReportDataSource();
        rds.Name = "dsReport_QLBaoVeTrongThang";
        rds.Value = GetResourceQLBaoVeTrongThang(dtFrom, dtTo);
        rpViewer.LocalReport.DataSources.Add(rds);
        rpViewer.Visible = true;
    }
    #endregion

    #region XeChuaThanhToan
    private DataTable GetResourceXeChuaThanhToan()
    {
        string sql = @"SELECT pf.Name AS HamXe, c.CustomerName AS HoTen, 
                        c.Address AS DiaChi, PlateNumber AS BienSo, pt.Name AS LoaiXe, 
                        pt.Price AS GiaTien, ToDate AS HetHan
                        FROM MonthCard mc
                        LEFT JOIN ParkingFloor pf ON mc.ParkingFloorId=pf.ParkingFloorID
                        LEFT JOIN PlateType pt ON mc.PlateTypeId=pt.ID
                        LEFT JOIN Customer c ON mc.CustomerId=c.CustomerID
                        WHERE ToDate<CAST(GETDATE() AS DATE)
                        ORDER BY pf.Name, c.CustomerName, PlateNumber ASC";

        SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, null);
        DataTable dt = new DataTable();
        dt.Load(reader);

        return dt;
    }

    private void XeChuaThanhToan(DateTime reportDate)
    {
        //provide local report information to viewer
        rpViewer.LocalReport.ReportPath = HttpContext.Current.Server.MapPath(Constants.REPORT_PATH + "XeChuaThanhToan.rdlc");
        //params
        ReportParameter[] param = new ReportParameter[2];
        param[0] = new ReportParameter("parm_ReportDate", reportDate.ToString("HH:mm dd/MM/yyyy"));
        param[1] = parm_Logo;
        rpViewer.LocalReport.SetParameters(param);
        //prepare report data source
        ReportDataSource rds = new ReportDataSource();
        rds.Name = "dsReport_XeChuaThanhToan";
        rds.Value = GetResourceXeChuaThanhToan();
        rpViewer.LocalReport.DataSources.Add(rds);
        rpViewer.Visible = true;
    }
    #endregion

    #region TKTongDoanhThuXe
    private DataTable GetResourceTKTongDoanhThuXe(DateTime dtFrom, DateTime dtTo)
    {
        string sql = @"SELECT pf.Name AS HamXe, 
                                vl.MienPhi AS VLMienPhi, vl.ThuTien AS VLThuTien, vl.SoTien AS VLDoanhThu,
                                mc.MienPhi AS VTMienPhi, mc.ThuTien AS VTThuTien, mc.SoTien AS VTDoanhThu
                                FROM
                        ParkingFloor pf
                        LEFT JOIN
                        (
	                        SELECT sl.ParkingFloor, sl.MienPhi, sl.ThuTien, st.SoTien FROM
	                        (
		                        SELECT ParkingFloor, [1] AS MienPhi, [0] AS ThuTien
		                        FROM
		                        (
			                        SELECT COUNT(PlateNumber) AS Total, ParkingFloor, IsVIPCard      
			                          FROM [EntryExit]
			                          WHERE ExitTime IS NOT NULL
			                          AND ExitTime BETWEEN @FromDate AND @ToDate		  
				                      AND IsMonthCard=0
			                        GROUP BY ParkingFloor, IsVIPCard
		                         )tmp
		                        PIVOT (SUM(Total) for IsVIPCard in ([0],[1]))AS test
	                        )sl--So luong xe mien phi, thu tien theo ham (ParkingFloor, MienPhi, ThuTien)
	                        LEFT JOIN
	                        (
		                        SELECT SUM(Price) AS SoTien, ParkingFloor    
		                        FROM [EntryExit]
		                        WHERE ExitTime IS NOT NULL
		                        AND ExitTime BETWEEN @FromDate AND @ToDate		  
		                        AND IsMonthCard=0
		                        GROUP BY ParkingFloor
	                        )st ON sl.ParkingFloor=st.ParkingFloor
                        	
                        )vl ON pf.Name=vl.ParkingFloor
                        LEFT JOIN
                        (
	                        SELECT sl.ParkingFloor, sl.MienPhi, sl.ThuTien, st.SoTien FROM
	                        (
		                        SELECT ParkingFloor, ISNULL([0],0) AS MienPhi, 
                                        (ISNULL([1],0)+ ISNULL([2],0) +ISNULL([3],0) + ISNULL([4],0)) AS ThuTien
                                FROM
                                (
                                    SELECT COUNT(e.PlateNumber) AS PlateNumber, e.ParkingFloor, mc.PlateTypeId FROM
                                    (
                                        SELECT PlateNumber, ParkingFloor      
                                        FROM [EntryExit]		  
                                        WHERE IsMonthCard=1
                                        AND ExitTime IS NOT NULL
                                        AND ExitTime BETWEEN @FromDate AND @ToDate
                                    )e
                                    INNER JOIN	
                                    (
                                        SELECT DISTINCT [PlateNumber], PlateTypeId      
                                        FROM [MonthCardHistory]
                                    )mc ON e.PlateNumber=mc.PlateNumber
                                    GROUP BY e.ParkingFloor, mc.PlateTypeId
                                )tmp
                                PIVOT (MAX(PlateNumber) for  [PlateTypeId] IN ([0], [1], [2], [3], [4]))AS test
	                        )sl
	                        LEFT JOIN
	                        (
		                        SELECT SUM(Price)As SoTien, pf.Name AS ParkingFloor      
		                        FROM [MonthCardHistory] mc
		                        LEFT JOIN ParkingFloor pf ON mc.ParkingFloorId=pf.ParkingFloorID
			                        WHERE @FromDate BETWEEN [FromDate] AND [ToDate]
			                        OR @ToDate BETWEEN [FromDate] AND [ToDate]
			                        OR (FromDate>= @FromDate AND ToDate <= @ToDate)
			                        OR (FromDate<= @FromDate AND ToDate >= @ToDate)
		                        GROUP BY pf.Name
	                        )st ON sl.ParkingFloor=st.ParkingFloor
                        )mc ON pf.Name=mc.ParkingFloor
                        ORDER BY HamXe";
        SqlParameter[] parms = new SqlParameter[2];
        parms[0] = new SqlParameter("FromDate", SqlDbType.DateTime);
        parms[0].Value = dtFrom;

        parms[1] = new SqlParameter("ToDate", SqlDbType.DateTime);
        parms[1].Value = dtTo;

        SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, parms);
        DataTable dt = new DataTable();
        dt.Load(reader);

        return dt;
    }

    private void TKTongDoanhThuXe(DateTime dtFrom, DateTime dtTo, DateTime reportDate)
    {
        //provide local report information to viewer
        rpViewer.LocalReport.ReportPath = HttpContext.Current.Server.MapPath(Constants.REPORT_PATH + "TKTongDoanhThuXe.rdlc");
        //params
        ReportParameter[] param = new ReportParameter[4];
        param[0] = new ReportParameter("parm_ReportDate", reportDate.ToString("HH:mm dd/MM/yyyy"));
        param[1] = new ReportParameter("parm_From", dtFrom.ToString("HH:mm dd/MM/yyyy"));
        param[2] = new ReportParameter("parm_To", dtTo.ToString("HH:mm dd/MM/yyyy"));
        param[3] = parm_Logo;
        rpViewer.LocalReport.SetParameters(param);
        //prepare report data source
        ReportDataSource rds = new ReportDataSource();
        rds.Name = "dsReport_TKTongDoanhThu";
        rds.Value = GetResourceTKTongDoanhThuXe(dtFrom, dtTo);
        rpViewer.LocalReport.DataSources.Add(rds);
        rpViewer.Visible = true;
    }
    #endregion

    #region TKSoLuongXeGroupByFloor
    private DataTable GetResourceTKSoLuongXeGroupByFloor(DateTime dtFrom, DateTime dtTo)
    {
        string sql = @"SELECT pf.Name AS HamXe, 
                        vl.XeSo AS VLXeSo, vl.TayGa AS VLTayGa, vl.CongTy AS VLCongTy, vl.Khach AS VLKhach,
                        mc.XeSo AS VTXeSo, mc.TayGa AS VTTayGa, mc.CongTy AS VTCongTy, mc.Khach AS VTKhach
                        FROM ParkingFloor pf
                        LEFT JOIN
                        (
                            SELECT ParkingFloor, 0 AS [TayGa], 0 AS [XeSo], 0 AS CongTy, COUNT(ID) As Khach      
	                        FROM [EntryExit]
	                        WHERE ExitTime IS NOT NULL
	                        AND ExitTime BETWEEN @FromDate AND @ToDate		  
	                        AND IsMonthCard=0
	                        GROUP BY ParkingFloor
                        )vl ON pf.Name=vl.ParkingFloor
                        LEFT JOIN
                        (
                            SELECT ParkingFloor, 0 AS Khach, [1] AS [TayGa], [2] AS [XeSo], [4] AS [CongTy]
                            FROM
                            (
                                SELECT COUNT(ID) AS ID, ParkingFloor, PlateTypeId
                                FROM
                                (
                                    SELECT ID, PlateNumber, ParkingFloor      
                                  FROM [EntryExit]		  
                                  WHERE IsMonthCard=1
                                  AND ExitTime IS NOT NULL
                                  AND ExitTime BETWEEN @FromDate AND @ToDate	
                                )e	  
                                  INNER JOIN
                                  (
                                      SELECT DISTINCT [PlateNumber], PlateTypeId      
                                            FROM [MonthCardHistory]
                                  )mc ON dbo.ufn_StripCharacters(UPPER(e.PlateNumber), '^a-z0-9')=dbo.ufn_StripCharacters(UPPER(mc.PlateNumber), '^a-z0-9')
                                GROUP BY ParkingFloor, PlateTypeId
                             )tmp
                            PIVOT (SUM(ID) for  [PlateTypeId] IN ([1], [2], [3], [4]))AS test	 
                        )mc ON pf.Name=mc.ParkingFloor
                        WHERE vl.ParkingFloor IS NOT NULL OR mc.ParkingFloor IS NOT NULL
                        ORDER BY pf.Name ASC";
        SqlParameter[] parms = new SqlParameter[2];
        parms[0] = new SqlParameter("FromDate", SqlDbType.DateTime);
        parms[0].Value = dtFrom;

        parms[1] = new SqlParameter("ToDate", SqlDbType.DateTime);
        parms[1].Value = dtTo;

        SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, parms);
        DataTable dt = new DataTable();
        dt.Load(reader);

        return dt;
    }

    private void TKSoLuongXeGroupByFloor(DateTime dtFrom, DateTime dtTo, DateTime reportDate)
    {
        //provide local report information to viewer
        rpViewer.LocalReport.ReportPath = HttpContext.Current.Server.MapPath(Constants.REPORT_PATH + "TKSoLuongXeGroupByFloor.rdlc");
        //params
        ReportParameter[] param = new ReportParameter[4];
        param[0] = new ReportParameter("parm_ReportDate", reportDate.ToString("HH:mm dd/MM/yyyy"));
        param[1] = new ReportParameter("parm_From", dtFrom.ToString("HH:mm dd/MM/yyyy"));
        param[2] = new ReportParameter("parm_To", dtTo.ToString("HH:mm dd/MM/yyyy"));
        param[3] = parm_Logo;
        rpViewer.LocalReport.SetParameters(param);
        //prepare report data source
        ReportDataSource rds = new ReportDataSource();
        rds.Name = "dsReport_TKSoLuongXe";
        rds.Value = GetResourceTKSoLuongXeGroupByFloor(dtFrom, dtTo);
        rpViewer.LocalReport.DataSources.Add(rds);
        rpViewer.Visible = true;
    }
    #endregion

    #region VangLaiEachFloor
    private DataTable GetResourceThongKeSoLuongXeVangLaiTheoHam(DateTime dtFrom, DateTime dtTo, string parkingFloor)
    {
        string sql = @"SELECT tmp2.ParkingFloor AS HamXe, tmp2.PlateNumber AS SoXe, 
                        tmp2.EntryTime AS NgayGioVao, tmp2.ExitTime AS NgayGioRa,
                        tmp1.XeSo
                        FROM
                        (
	                        SELECT SUM(Price) AS XeSo, ID, PlateNumber       
	                        FROM [EntryExit]
	                        WHERE ExitTime IS NOT NULL
	                        AND ExitTime BETWEEN @FromDate AND @ToDate		  
	                        AND IsMonthCard=0
	                        GROUP BY ID, PlateNumber
                        )tmp1
                        INNER JOIN
                        (
	                        SELECT [ID], PlateNumber
	                        ,[EntryTime]
	                        ,[ExitTime]      
	                        ,[CardType]
	                        ,[ParkingFloor]      
	                        FROM [EntryExit]
	                        WHERE ExitTime IS NOT NULL
	                        AND ExitTime BETWEEN @FromDate AND @ToDate
	                        AND IsMonthCard=0
                        )tmp2 ON tmp1.ID=tmp2.ID
                        WHERE ParkingFloor=@ParkingFloor
                        ORDER BY tmp2.ExitTime DESC, tmp2.EntryTime DESC";
        SqlParameter[] parms = new SqlParameter[3];
        parms[0] = new SqlParameter("FromDate", SqlDbType.DateTime);
        parms[0].Value = dtFrom;

        parms[1] = new SqlParameter("ToDate", SqlDbType.DateTime);
        parms[1].Value = dtTo;

        parms[2] = new SqlParameter("ParkingFloor", SqlDbType.NVarChar);
        parms[2].Value = parkingFloor;

        SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, parms);
        DataTable dt = new DataTable();
        dt.Load(reader);

        return dt;
    }

    private void ThongKeSoLuongXeVangLaiTheoHam(DateTime dtFrom, DateTime dtTo, DateTime reportDate, string parkingFloor)
    {
        //provide local report information to viewer
        rpViewer.LocalReport.ReportPath = HttpContext.Current.Server.MapPath(Constants.REPORT_PATH + "VangLaiEachFloor.rdlc");
        //params
        ReportParameter[] param = new ReportParameter[5];
        param[0] = new ReportParameter("parm_ReportDate", reportDate.ToString("HH:mm dd/MM/yyyy"));
        param[1] = new ReportParameter("parm_From", dtFrom.ToString("HH:mm dd/MM/yyyy"));
        param[2] = new ReportParameter("parm_To", dtTo.ToString("HH:mm dd/MM/yyyy"));
        param[3] = new ReportParameter("parm_Floor", parkingFloor);
        param[4] = parm_Logo;
        rpViewer.LocalReport.SetParameters(param);
        //prepare report data source
        ReportDataSource rds = new ReportDataSource();
        rds.Name = "dsReport_VangLaiGroupFloor";
        rds.Value = GetResourceThongKeSoLuongXeVangLaiTheoHam(dtFrom, dtTo, parkingFloor);
        rpViewer.LocalReport.DataSources.Add(rds);
        rpViewer.Visible = true;
    }
    #endregion

    #region VangLaiGroupByFloor
    private DataTable GetResourceThongKeSoLuongXeVangLai(DateTime dtFrom, DateTime dtTo)
    {
        string sql = @"SELECT tmp2.ParkingFloor AS HamXe, tmp2.PlateNumber AS SoXe, 
                        tmp2.EntryTime AS NgayGioVao, tmp2.ExitTime AS NgayGioRa,
                        tmp1.XeSo
                        FROM
                        (
                            SELECT SUM(Price) AS XeSo, ID, PlateNumber 
                                  FROM [EntryExit]
                                  WHERE ExitTime IS NOT NULL
                                  AND ExitTime BETWEEN @FromDate AND @ToDate		  
                                  AND IsMonthCard=0
                                  GROUP BY ID, PlateNumber
                        )tmp1
                        INNER JOIN
                        (
                            SELECT [ID], PlateNumber
                              ,[EntryTime]
                              ,[ExitTime]      
                              ,[CardType]
                              ,[ParkingFloor]      
                            FROM [EntryExit]
                            WHERE ExitTime IS NOT NULL
                                  AND ExitTime BETWEEN @FromDate AND @ToDate
                                  AND IsMonthCard=0
                        )tmp2 ON tmp1.ID=tmp2.ID
                        ORDER BY tmp2.ParkingFloor ASC, tmp2.ExitTime DESC, tmp2.EntryTime DESC";
        SqlParameter[] parms = new SqlParameter[2];
        parms[0] = new SqlParameter("FromDate", SqlDbType.DateTime);
        parms[0].Value = dtFrom;

        parms[1] = new SqlParameter("ToDate", SqlDbType.DateTime);
        parms[1].Value = dtTo;

        SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, parms);
        DataTable dt = new DataTable();
        dt.Load(reader);

        return dt;
    }

    private void ThongKeSoLuongXeVangLai(DateTime dtFrom, DateTime dtTo, DateTime reportDate)
    {
        //provide local report information to viewer
        rpViewer.LocalReport.ReportPath = HttpContext.Current.Server.MapPath(Constants.REPORT_PATH + "VangLaiGroupByFloor.rdlc");
        //params
        ReportParameter[] param = new ReportParameter[4];
        param[0] = new ReportParameter("parm_ReportDate", reportDate.ToString("HH:mm dd/MM/yyyy"));
        param[1] = new ReportParameter("parm_From", dtFrom.ToString("HH:mm dd/MM/yyyy"));
        param[2] = new ReportParameter("parm_To", dtTo.ToString("HH:mm dd/MM/yyyy"));
        param[3] = parm_Logo;
        rpViewer.LocalReport.SetParameters(param);
        //prepare report data source
        ReportDataSource rds = new ReportDataSource();
        rds.Name = "dsReport_VangLaiGroupFloor";
        rds.Value = GetResourceThongKeSoLuongXeVangLai(dtFrom, dtTo);
        rpViewer.LocalReport.DataSources.Add(rds);
        rpViewer.Visible = true;
    }
    #endregion

    #region Datasource for reports

    /// <summary>
    /// For report 01
    /// </summary>
    /// <returns></returns>
    private DataTable GetResourceReport01(DateTime dtFrom, DateTime dtTo)
    {
        string sql = @"SELECT [CustomerName] AS TenKH
                              ,[Address] AS DiaChi
                              ,[PlateNumber] AS BienSo
                              ,[CarColor] AS MauXe
                              ,[ParkingFloor] AS HamXe
                              ,[FromDate] AS TuNgay
                              ,[ToDate] AS DenNgay
                              ,CASE WHEN [TayGa] IS NULL THEN 0 ELSE [TayGa] END AS TayGa
                              ,CASE WHEN XeSo IS NULL THEN 0 ELSE XeSo END AS XeSo
                              ,CASE WHEN CongTy IS NULL THEN 0 ELSE CongTy END AS CongTy
                              ,[MienPhi]
                          FROM [MonthCardHistoryPrePaid]
                            WHERE @FromDate BETWEEN [FromDate] AND [ToDate]
                                    OR @ToDate BETWEEN [FromDate] AND ToDate
                                    OR (FromDate>= @FromDate AND ToDate <= @ToDate)
                                    OR (FromDate<= @FromDate AND ToDate >= @ToDate)
                            ORDER BY ParkingFloor, CustomerName, [Address], [PlateNumber], [FromDate], [ToDate]";
        SqlParameter[] parms = new SqlParameter[2];
        parms[0] = new SqlParameter("FromDate", SqlDbType.DateTime);
        parms[0].Value = dtFrom;

        parms[1] = new SqlParameter("ToDate", SqlDbType.DateTime);
        parms[1].Value = dtTo;

        SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, parms);
        DataTable dt = new DataTable();
        dt.Load(reader);

        return dt;
    }

    /// <summary>
    /// Report 02
    /// </summary>
    /// <param name="dtFrom"></param>
    /// <param name="dtTo"></param>
    /// <returns></returns>
    private DataTable GetResourceReport02(DateTime dtFrom, DateTime dtTo, string parkingFloor)
    {
        string sql = @"SELECT [CustomerName] AS TenKH
                              ,[Address] AS DiaChi
                              ,[PlateNumber] AS BienSo
                              ,[CarColor] AS MauXe
                              ,[ParkingFloor] AS HamXe
                              ,[FromDate] AS TuNgay
                              ,[ToDate] AS DenNgay
                              ,CASE WHEN [TayGa] IS NULL THEN 0 ELSE [TayGa] END AS TayGa
                              ,CASE WHEN XeSo IS NULL THEN 0 ELSE XeSo END AS XeSo
                              ,CASE WHEN CongTy IS NULL THEN 0 ELSE CongTy END AS CongTy
                              ,[MienPhi]
                          FROM [MonthCardHistoryPrePaid]
                            WHERE ParkingFloor=@ParkingFloor
                                    AND (@FromDate BETWEEN [FromDate] AND [ToDate]
                                    OR @ToDate BETWEEN [FromDate] AND [ToDate]
                                    OR (FromDate>= @FromDate AND ToDate <= @ToDate)
                                    OR (FromDate<= @FromDate AND ToDate >= @ToDate))
                            ORDER BY CustomerName, [Address], [PlateNumber], [FromDate], [ToDate]";
        SqlParameter[] parms = new SqlParameter[3];
        parms[0] = new SqlParameter("ParkingFloor", SqlDbType.NVarChar);
        parms[0].Size = parkingFloor.Length;
        parms[0].Value = parkingFloor;

        parms[1] = new SqlParameter("FromDate", SqlDbType.DateTime);
        parms[1].Value = dtFrom;

        parms[2] = new SqlParameter("ToDate", SqlDbType.DateTime);
        parms[2].Value = dtTo;

        SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql, parms);
        DataTable dt = new DataTable();
        dt.Load(reader);

        return dt;
    }

    #endregion 

  #region Events
    /// <summary>
    /// Page load
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
  protected void Page_Load(object sender, EventArgs e)
  {
      try
      {
          if (!Page.IsPostBack)
          {
              txtFrom.Text = DateTime.Now.ToString("dd/MM/yyyy");
              txtTo.Text = DateTime.Now.ToString("dd/MM/yyyy");
              txtFromHour.Text = "08:00";
              txtToHour.Text = DateTime.Now.ToString("17:00");
          }
      }
      catch (Exception ex)
      {
          lblMsg.Text = Constants.ERROR_MSG;
          if (logger.IsErrorEnabled) { logger.Error(ex.Message); }
      }  
  }

    /// <summary>
    /// Reset click
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
  protected void btnReset_Click(object sender, ImageClickEventArgs e)
  {

  }

    /// <summary>
    /// View click
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
  protected void btnView_Click(object sender, ImageClickEventArgs e)
  {
      try
      {
          Export();
      }
      catch (Exception ex)
      {
          lblMsg.Text = Constants.ERROR_MSG;
          if (logger.IsErrorEnabled) { logger.Error(ex.Message); }
      } 
  }

  protected void ddlReport_SelectedIndexChanged(object sender, EventArgs e)
  {
      try
      {
          if (ddlReport.SelectedValue.Equals("02") || ddlReport.SelectedValue.Equals("VangLai"))
          {
              LoadFloor();
          }
          else if (ddlReport.SelectedValue.Equals("QLBaoVeTrongThang") || ddlReport.SelectedValue.Equals("QLBaoVeTrongNam"))
          {
              LoadEmployee();
          } 
          else
          {
              trHam.Visible = false;
              trNhanVien.Visible = false;
          }

          trDate.Visible = !ddlReport.SelectedValue.Equals("XeChuaThanhToan");
      }
      catch (Exception ex)
      {
          lblMsg.Text = Constants.ERROR_MSG;
          if (logger.IsErrorEnabled) { logger.Error(ex.Message); }
      }

  }
  #endregion
  
}